const express = require("express");
const router = express.Router();
const nodexlsx = require("node-xlsx"); //引入nodexlsx
const stream = require("stream"); // 引入通道
const fs = require("fs");
const path = require("path");

const { local } = require("../url");

var mysql = require("mysql");
const env = {
  host: "111.231.22.97",
  user: "wangs-web",
  password: "HsTjw82zF7f6fjWY",
  database: "wangs-web", //数据库名字
};

const urls = path.join(__dirname, "/file/");
router.use(express.static(urls));

//过滤
const filters = (e) => {
  let objs = {};
  for (let keys in e) {
    if (e[keys] != "" && e[keys] != undefined && e[keys] != null) {
      objs[keys] = e[keys];
    }
  }
  return Object.keys(objs);
};

/* 管理员审核日报 */
const sings = (req, res, pools) => {
  const { projectCode, id } = req.body;
  const queryArr = ["1", projectCode, id];
  const sql =
    "update ddetaillist set status = ? where projectCode = ? and id = ?";
  pools.query(sql, queryArr, (err, result) => {
    if (err) {
      res.status(500);
      console.log(err, "error");
    } else {
      if (result.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: "success",
        });
      } else {
        res.send({
          code: 500,
          msg: "error",
        });
      }
    }
  });
};

const containId = (req, res, pools) => {
  const sql = "update ddetaillist set status = ? where projectCode = ?";
  const { projectCode } = req.body;
  pools.query(sql, ["1", projectCode], (err, result) => {
    if (err) {
      res.status(500);
      console.log(err, "error");
    } else {
      if (result.affectedRows >= 1) {
        res.send({
          code: 200,
          msg: "success",
        });
      } else {
        res.send({
          code: 500,
          msg: "error",
        });
      }
    }
  });
};
router.post("/examineDaliy", (req, res) => {
  let pools = mysql.createPool(env);
  /* 有id的情况为单条审核 */
  if (req.body.id) {
    sings(req, res, pools);
  } else {
    /* 无id的情况下是一键审核 */
    containId(req, res, pools);
  }
});

/* 管理员导出日报 */

const findName = (req, arr) => {
  return new Promise(async (reslove, reject) => {
    console.log(arr, "arr");
    let resArr = arr.filter((el) => {
      return el.name == req.body.name;
    });
    let arrp = await changeStatus(resArr);
    reslove(arrp);
  });
};

/* 转化status */
const changeStatus = (arr) => {
  return new Promise((reslove, reject) => {
    let arr1 = [];
    arr.forEach((el) => {
      el.status == 1 ? (el.status = "已审核") : (el.status = "未审核");
    });
    reslove(arr);
  });
};

const admin = async (req, res) => {
  const arrs = filters(req.body);
  let pools = mysql.createPool(env);
  let xlsxArr = [];
  xlsxArr.push([
    "项目编号",
    "日报编号",
    "日报内容",
    "填报日期",
    "填报人",
    "状态",
  ]);
  let dates = new Date().getTime();
  let urs = urls + dates + ".xlsx";
  /* 只有projectCode、pageNo、pageSize */
  if (arrs.length == 1) {
    let sql = "select * from ddetaillist where projectCode = ?";
    pools.query(sql, [req.body.projectCode], async (err, result) => {
      if (err) {
        res.status(500);
        console.log(err, "error");
      }
      if (result.length > 0) {
        let newArr = await changeStatus(result);
        newArr.forEach((el) => {
          let arr = [];
          arr.push(
            el.projectCode,
            el.id,
            el.content,
            el.date,
            el.name,
            el.status
          );
          xlsxArr.push(arr);
        });
        const Buffer = nodexlsx.build([{ name: "sheet1", data: xlsxArr }]);
        fs.writeFile(urs, Buffer, (err) => {
          if (err) {
            console.log(err);
          } else {
            res.send({
              code: 200,
              msg: "success",
              data: {
                url: local + dates + ".xlsx",
              },
            });
          }
        });
      } else {
        res.send({
          code: 500,
          msg: "日报数为0",
        });
      }
    });
  } else if (arrs.includes("name")) {
    /* 根据成员名称*/
    let sql = "select * from ddetaillist where projectCode = ?";
    pools.query(sql, [req.body.projectCode], async (err, resp) => {
      if (err) {
        res.status(500);
        console.log(err, "error");
      }
      let resArr = await findName(req, resp);
      resArr.forEach((el) => {
        let arr = [];
        arr.push(
          el.projectCode,
          el.id,
          el.content,
          el.date,
          el.name,
          el.status
        );
        xlsxArr.push(arr);
      });
      const Buffer = nodexlsx.build([{ name: "sheet1", data: xlsxArr }]);
      fs.writeFile(urs, Buffer, (err) => {
        if (err) {
          console.log(err);
        } else {
          res.send({
            code: 200,
            msg: "success",
            data: {
              url: local + dates + ".xlsx",
            },
          });
        }
      });
    });
  }
};

router.post("/exportExcel", (req, res) => {
  admin(req, res);
});

//获取username列表

//转化成中文名称
const changeToName = (str) => {
  return new Promise((reslove, reject) => {
    let arr = str.split("/");
    let newArr = [];
    console.log(arr, "arr");
    let pools = mysql.createPool(env);
    let sql = "select * from userlist";
    pools.query(sql, (err, resk) => {
      arr.forEach((el) => {
        resk.forEach((ele) => {
          if (el == ele.id) {
            let obj = {
              label: ele.name,
              value: ele.id,
            };
            newArr.push(obj);
          }
        });
      });
      reslove(newArr);
    });
  });
};

const getExportUser = (req, res) => {
  const { projectCode } = req.query;
  let sql = "select member from dtotallist where projectCode = ?";
  let pools = mysql.createPool(env);
  pools.query(sql, [projectCode], async (err, resl) => {
    if (err) {
      resl.status(500);
      console.log(err, "err");
    }
    console.log(resl);
    let arrs = await changeToName(resl[0]["member"]);
    res.send({
      code: 200,
      msg: "success",
      data: arrs,
    });
  });
};
router.get("/getExportUser", (req, res) => {
  getExportUser(req, res);
});

module.exports = router;
